package db.newversion;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Observable;

import javax.swing.JOptionPane;

import com.sun.org.apache.xpath.internal.functions.WrongNumberArgsException;

import core.Constants;
import core.GuiComponentLabels;

public class DistancesDB extends Observable implements IDataBase < Combination2, LinkedList < Integer > > {
	private static DistancesDB instance = null;
	private HashMap < Combination2, LinkedList < Integer > > buffer;

	public static DistancesDB getInstance() {
		if (instance == null) {
			instance = new DistancesDB();
		}
		return instance;
	}

	private DistancesDB() {
		try {
			this.createTable();
		} catch (SQLException e1) {

		}
		try {
			this.importAllTournamentTypes();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "An error occurred when importing the tournament types", "Error", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	/**
	 * @param toInsert
	 *            a list of distances to insert
	 * @param key
	 *            [0] = tournamentType key[1] = bowCathegory key[2] = ageClass
	 *            key[3] = gender
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean insert(LinkedList < Integer > toInsert, Object... key) throws SQLException {
		String tournamentType = key [0].toString();
		String bowCathegory = key [1].toString();
		String ageClass = key [2].toString();
		String gender = key [3].toString();
		Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);
		if (toInsert == null) {
			toInsert = new LinkedList < Integer >();
			toInsert.add(0);
		}

		if (this.buffer.containsKey(combination)) {
			this.update(toInsert, key);
		} else {
			PreparedStatement psInsert = GeneralDB.getInstance().getConnection().prepareStatement("insert into " + Constants.TOURNAMENt_TYPE_TABLENAME + " values (?, ?, ?, ?, ?)");

			psInsert.setString(1, tournamentType);
			psInsert.setString(2, bowCathegory);
			psInsert.setString(3, ageClass);
			psInsert.setString(4, gender);
			psInsert.setString(5, this.listToString(toInsert));

			psInsert.executeUpdate();

			this.buffer.put(combination, toInsert);

			this.setChanged();
			this.notifyObservers();
		}
		return true;
	}

	public void insertTournamentType(String tournamentType) {
		String [] tournamentTypes = this.getAll(DbEntry.TOURNAMENT_TYPE);
		for (int i = 0; i < tournamentTypes.length; i++) {
			if (tournamentTypes [i].equals(tournamentType)) {
				return;
			}
		}

		JOptionPane.showMessageDialog(null, GuiComponentLabels.WAIT_MESSAGE);

		String [] bowCathegories = ArchersDB.getInstance().getAll(DbEntry.BOWCATHEGORY);
		String [] ageClasses = ArchersDB.getInstance().getAll(DbEntry.AGECLASS);
		String [] genders = ArchersDB.getInstance().getAll(DbEntry.GENDER);

		for (int i = 0; i < bowCathegories.length; i++) {
			for (int j = 0; j < ageClasses.length; j++) {
				for (int k = 0; k < genders.length; k++) {
					try {
						this.insert(null, tournamentType, bowCathegories [i], ageClasses [j], genders [k]);
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
	}

	/**
	 * @param toUpdate
	 *            a list of distances to update
	 * @param key
	 *            [0] = tournamentType key[1] = bowCathegory key[2] = ageClass
	 *            key[3] = gender
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean update(LinkedList < Integer > toUpdate, Object... key) throws SQLException {
		String tournamentType = key [0].toString();
		String bowCathegory = key [1].toString();
		String ageClass = key [2].toString();
		String gender = key [3].toString();
		Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);

		PreparedStatement psUpdate = GeneralDB.getInstance().getConnection()
				.prepareStatement("update " + Constants.TOURNAMENt_TYPE_TABLENAME + " set distances=? WHERE tournamentType=? AND bowCathegory=? AND ageClass=? AND gender=?");

		psUpdate.setString(1, this.listToString(this.buffer.get(combination)));
		psUpdate.setString(2, combination.getTournamentType());
		psUpdate.setString(3, combination.getBowCathegory());
		psUpdate.setString(4, combination.getAgeClass());
		psUpdate.setString(5, combination.getGender());

		psUpdate.executeUpdate();

		this.buffer.put(combination, toUpdate);

		this.setChanged();
		this.notifyObservers();

		return true;
	}

	/**
	 * @param toDelete
	 *            a list of distances to delete
	 * @param key
	 *            [0] = tournamentType key[1] = bowCathegory key[2] = ageClass
	 *            key[3] = gender
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean delete(LinkedList < Integer > toDelete, Object... key) throws SQLException {
		String tournamentType = key [0].toString();
		String bowCathegory = key [1].toString();
		String ageClass = key [2].toString();
		String gender = key [3].toString();
		Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);

		PreparedStatement psDelete = GeneralDB.getInstance().getConnection()
				.prepareStatement("delete from " + Constants.TOURNAMENt_TYPE_TABLENAME + " where tournamentType=? AND bowCathegory=? AND ageClass=? AND gender=?");
		psDelete.setString(1, tournamentType);
		psDelete.setString(2, bowCathegory);
		psDelete.setString(3, ageClass);
		psDelete.setString(4, gender);
		psDelete.executeUpdate();

		this.buffer.remove(combination);

		this.setChanged();
		this.notifyObservers();

		return true;
	}

	/**
	 * @return all entries of the DB
	 */
	@Override
	public HashMap < Combination2, LinkedList < Integer >> getAll() {
		HashMap < Combination2, LinkedList < Integer > > toReturn = new HashMap < Combination2, LinkedList < Integer > >();
		Iterator < Combination2 > iterator = this.buffer.keySet().iterator();
		while (iterator.hasNext()) {
			Combination2 tmp = iterator.next();
			toReturn.put(tmp, this.buffer.get(tmp));
		}
		return toReturn;
	}

	/**
	 * @param key
	 *            [0] = tournamentType key[1] = bowCathegory key[2] = ageClass
	 *            key[3] = gender
	 * @return the corresponding list of distances
	 * @throws SQLException
	 */
	@Override
	public LinkedList < Integer > getElement(Object... key) throws WrongNumberArgsException {
		String tournamentType = key [0].toString();
		String bowCathegory = key [1].toString();
		String ageClass = key [2].toString();
		String gender = key [3].toString();
		Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);
		if (this.buffer.containsKey(combination)) {
			return this.buffer.get(combination);
		} else {
			LinkedList < Integer > toReturn = new LinkedList < Integer >();
			toReturn.add(0);
			return toReturn;
		}

	}

	/**
	 * Creates the tournamentType SQL-Table of the form [tournamentType,
	 * bowCathegory, ageClass, gender, distances]
	 */
	@Override
	public void createTable() throws SQLException {
		GeneralDB
				.getInstance()
				.getConnection()
				.createStatement()
				.execute(
						"create table " + Constants.TOURNAMENt_TYPE_TABLENAME
								+ "(tournamentType varchar(40), bowCathegory varchar(40), ageClass varchar(40), gender varchar(40), distances varchar(300))");
	}

	/**
	 * SQL drop table tournamentType
	 * @throws SQLException
	 */
	@Override
	public void dropTable() throws SQLException {
		Statement statement = GeneralDB.getInstance().getConnection().createStatement();
		statement.execute("drop table " + Constants.TOURNAMENt_TYPE_TABLENAME);
		instance = null;
	}

	/**
	 * loads all entries from the DB to the buffer
	 * @throws SQLException
	 */
	private void importAllTournamentTypes() throws SQLException {
		this.buffer = new HashMap < Combination2, LinkedList < Integer > >();

		ResultSet resultSet = GeneralDB.getInstance().getConnection().createStatement().executeQuery("SELECT * FROM " + Constants.TOURNAMENt_TYPE_TABLENAME);
		while (resultSet.next()) {
			String tournamentType = resultSet.getString(1);
			String bowCathegory = resultSet.getString(2);
			String ageClass = resultSet.getString(3);
			String gender = resultSet.getString(4);
			Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);
			this.buffer.put(combination, this.stringToList(resultSet.getString(5)));

		}
	}

	/**
	 * @param string
	 *            a list of Integers separated by semicolons
	 * @return an array of Integers
	 */
	private LinkedList < Integer > stringToList(String string) {
		String [] tmp = string.split(";");
		LinkedList < Integer > toReturn = new LinkedList < Integer >();
		for (int i = 0; i < tmp.length; i++) {
			toReturn.add(Integer.parseInt(tmp [i]));
		}
		return toReturn;
	}

	/**
	 * @param toConvert
	 *            a LinkesList of type String
	 * @return the given List, separated by semicolons
	 */
	private String listToString(LinkedList < Integer > toConvert) {
		StringBuilder output = new StringBuilder();
		for (int i = 0; i < toConvert.size(); i++) {
			output.append(toConvert.get(i));
			output.append(";");
		}
		return output.toString();
	}

	/**
	 * @param tournamentType
	 * @param bowCathegory
	 * @param ageClass
	 * @param gender
	 * @return the number of rounds assigned to this combination
	 * @throws SQLException
	 */
	public int getNumberOfRounds(String tournamentType, String bowCathegory, String ageClass, String gender) throws SQLException {
		Combination2 combination = new Combination2(tournamentType, bowCathegory, ageClass, gender);
		if (this.buffer.containsKey(combination)) {
			return this.buffer.get(combination).size();
		} else {
			return 0;
		}
	}

	/**
	 * @param entry
	 *            the entry to retrieve
	 * @return an array of Strings with unique entries
	 */
	public String [] getAll(DbEntry entry) {
		HashSet < String > entries = new HashSet < String >();
		Iterator < Combination2 > iterator = this.buffer.keySet().iterator();
		while (iterator.hasNext()) {
			Combination2 tmp = iterator.next();

			switch (entry) {
				case TOURNAMENT_TYPE:
					entries.add(tmp.getTournamentType());
			}
		}

		String [] toReturn = new String [entries.size()];
		entries.toArray(toReturn);
		return toReturn;
	}
}
